import pandas as pd
import pymysql
import os
import datetime


"""
一级类目:active
二级类目：sweatshirts
对亚马逊昨日排行榜进行统计处理
1 取昨日全天，不同大类和子类数据
2 对每个商品sort分进行汇总，并倒序排序
"""

pd.set_option('display.max_columns', 1000)
pd.set_option('display.max_colwidth', 1000)   # 完整显示行长度
pd.set_option('display.width', 1000)          # 最大显示宽度
# 打印所有隐藏行
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
db = pymysql.connect(user=os.environ["NZMYSQL_USER"], password=os.environ["NZMYSQL_PASSWORD"],
                                 host=os.environ["NZMYSQL_HOST"], port=13306, db="spider_db")
cursor = db.cursor()
# 定义要提取的一级类目和二级类目
one_lever = "active"
second_lever = "Active_Sweatshirts"
# 取出昨日数据，interval 0为当日数据，1为昨日数据
sql = "SELECT * FROM amazon_sort WHERE date=date_sub(curdate(),interval 1 day) AND one_lever='{}' AND " \
      "second_lever='{}';".format(one_lever, second_lever)
datas = pd.read_sql(sql=sql, con=db)
# 取出URL唯一值
product_id_ls = datas["product_id"].unique()
pro_id = []
sort_sum = []
now_date = datetime.date.today()      # 写入日期
# 根据product_id计算排名
for p_id in product_id_ls:
    res = datas.loc[datas["product_id"] == p_id]
    pro_url = "https://www.amazon.com//-/zh/dp/{}/".format(p_id)
    pro_id.append(res["product_id"].unique())
    sort_sum.append(res["sort"].sum())
    sort = res["sort"].sum()
    # 数据结果入库
    db2 = pymysql.connect(user=os.environ["NZMYSQL_USER"], password=os.environ["NZMYSQL_PASSWORD"],
                          host=os.environ["NZMYSQL_HOST"], port=13306, db="calculation_results")
    cursor2 = db2.cursor()
    try:
        sql2 = "INSERT INTO amazon_sort_rest(pro_id, pro_url, now_date, one_lever, second_lever, sort)VALUES(%s,%s,%s,%s,%s,%s)"
        values2 = (p_id, pro_url, now_date, one_lever, second_lever, sort)
        cursor2.execute(sql2, values2)
    except Exception:
        sql2 = "REPLACE INTO amazon_sort_rest(pro_id, pro_url, now_date, one_lever, second_lever, sort)VALUES(%s,%s,%s,%s,%s,%s)"
        values2 = (p_id, pro_url, now_date, one_lever, second_lever, sort)
        cursor2.execute(sql2, values2)
    db2.commit()
    cursor2.close()
    db2.close()
columns_ = ["product_id"]
result = pd.DataFrame(pro_id, columns=columns_)
result["sort_sum"] = sort_sum
result = result.sort_values(by="sort_sum", ascending=True)
print(result)




